﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using EntityObjects;

namespace DAL_V1.App_Code.DBBroker
{
    public class RequisitionDBBroker
    {
        private  SSISDataSet ds;
        private  SSISDataSetTableAdapters.RequisitionTableAdapter tda;
        private  SSISDataSetTableAdapters.RequisitionDetailsTableAdapter detailstda;
        private SSISDataSetTableAdapters.ConsolidateRetrivalTableAdapter consolidatetda;
        private SSISDataSetTableAdapters.RetrivalTableAdapter retrivaltda;
        private int index;
        private List<Requisition> req_list;
        private List<RequisitionDetails> reqDetails_list;
        private Requisition req;
        private RequisitionDetails reqDetails;
        private SSISDataSet.RequisitionDataTable req_td;
        private SSISDataSet.RequisitionDetailsDataTable reqDetails_td;
        private SSISDataSet.ConsolidateRetrivalDataTable consolidateRetrival_td;
        private SSISDataSet.RetrivalDataTable retrival_td;

        //singleton
        private static bool instance_flag = false;
        private static RequisitionDBBroker instance;

        private RequisitionDBBroker()
        {
            ds = new SSISDataSet();
            tda = new SSISDataSetTableAdapters.RequisitionTableAdapter();
            detailstda = new SSISDataSetTableAdapters.RequisitionDetailsTableAdapter();
            consolidatetda = new SSISDataSetTableAdapters.ConsolidateRetrivalTableAdapter();
            retrivaltda = new SSISDataSetTableAdapters.RetrivalTableAdapter();

            tda.Fill(ds.Requisition);
            detailstda.Fill(ds.RequisitionDetails);
            
        }

        public static RequisitionDBBroker getRequisitionDBBroker()
        {
            if (!instance_flag)
            {
                instance = new RequisitionDBBroker();
                instance_flag = true;

                return instance;
            }
            else
            {
                return instance;
            }
        }

        public List<Requisition> selectAllRequisition()
        {
            req_list = new List<Requisition>();
            for (int i = 0; i < ds.Requisition.Rows.Count; i++)
            {
                req = new Requisition();
                req.ReqFormId = ds.Requisition[i].ReqFormId;
                req.DeptId = ds.Requisition[i].DeptId;
                req.EmpId = ds.Requisition[i].EmpId;
                req.RequestedDate = ds.Requisition[i].RequestedDate;
                req.Status = ds.Requisition[i].Status;
                req.ApprovedOn = ds.Requisition[i].ApprovedOn;
                req.WeekNo = ds.Requisition[i].WeekNo;

                //Details
                reqDetails_list = selectReqDetailsByReqId(ds.Requisition[i].ReqFormId);
                req.ReqDetails_list = reqDetails_list;

                req_list.Add(req);
            }
            return req_list;
        }

        public DataTable selectAllRequisition_DT()
        {
            return ds.Requisition;
        }

        public int addRequisition(Requisition rq)
        {
            SSISDataSet.RequisitionRow newRow = ds.Requisition.NewRequisitionRow();
            newRow.ReqFormId = rq.ReqFormId;
            newRow.DeptId = rq.DeptId;
            newRow.EmpId = rq.EmpId;
            newRow.RequestedDate = rq.RequestedDate;
            newRow.Status = rq.Status;
            newRow.ApprovedOn = rq.ApprovedOn;
            newRow.WeekNo = rq.WeekNo;
                     
            ds.Requisition.Rows.Add(newRow);
            return tda.Update(ds.Requisition);
        }

        //private int getIndex(int Id)
        //{
        //    SSISDataSet.RequisitionRow row = ds.Requisition.FindByReqFormId(Id);
        //    return ds.Requisition.Rows.IndexOf(row);
        //}

        private SSISDataSet.RequisitionRow getRowById(int Id)
        {
            return ds.Requisition.FindByReqFormId(Id);
        }

        public int updateRequisition(Requisition rq, int Id)
        {
            SSISDataSet.RequisitionRow row = getRowById(Id);
            if (row != null)
            {
                row.ReqFormId = rq.ReqFormId;
                row.DeptId = rq.DeptId;
                row.EmpId = rq.EmpId;
                row.RequestedDate = rq.RequestedDate;
                row.Status = rq.Status;
                row.ApprovedOn = rq.ApprovedOn;
                row.WeekNo = rq.WeekNo;
            }
        
            return tda.Update(ds.Requisition);
        }

        public int deleteRequisition(int Id)
        {
            SSISDataSet.RequisitionRow row = getRowById(Id);
            if (row != null)
            {
                ds.Requisition.Rows.Remove(row);
            }
            return tda.Update(ds.Requisition);
        }

        public Requisition selectRequisition(int Id)
        {
            SSISDataSet.RequisitionRow r = getRowById(Id);
            if (r != null)
            {
                req = new Requisition();
                req.ReqFormId = r.ReqFormId;
                req.DeptId = r.DeptId;
                req.EmpId = r.EmpId;
                req.RequestedDate = r.RequestedDate;
                req.Status = r.Status;
                req.ApprovedOn = r.ApprovedOn;
                req.WeekNo = r.WeekNo;

                //Details
                reqDetails_list = selectReqDetailsByReqId(r.ReqFormId);
                req.ReqDetails_list = reqDetails_list;

                return req;
            }
            else
            {
                return null;
            }
        }

        //Requistion Details
        public List<RequisitionDetails> selectAllRequisitionDetails()
        {
            reqDetails_list = new List<RequisitionDetails>();
            for (int i = 0; i < ds.RequisitionDetails.Rows.Count; i++)
            {
                reqDetails = new RequisitionDetails();
                reqDetails.ReqFormId = ds.RequisitionDetails[i].ReqFormId;
                reqDetails.ItemId = ds.RequisitionDetails[i].ItemId;
                reqDetails.RequestedQty = ds.RequisitionDetails[i].RequestedQty;
                reqDetails.ReceivedQty = ds.RequisitionDetails[i].ReceivedQty;
                reqDetails_list.Add(reqDetails);
            }
            return reqDetails_list;
        }

        public DataTable selectAllRequisitionDetails_DT()
        {
            return ds.RequisitionDetails;
        }

        public int addRequisitionDetails(RequisitionDetails rqDetails)
        {
            SSISDataSet.RequisitionDetailsRow newRow = ds.RequisitionDetails.NewRequisitionDetailsRow();
            newRow.ReqFormId = rqDetails.ReqFormId;
            newRow.ItemId = rqDetails.ItemId;
            newRow.RequestedQty = rqDetails.RequestedQty;
            newRow.ReceivedQty = rqDetails.ReceivedQty;

            ds.RequisitionDetails.Rows.Add(newRow);
            return detailstda.Update(ds.RequisitionDetails);
        }

        //private int getIndex(int formId, String itemId)
        //{
        //    SSISDataSet.RequisitionDetailsRow row = ds.RequisitionDetails.FindByReqFormIdItemId(formId, itemId);
        //    return ds.RequisitionDetails.Rows.IndexOf(row);
        //}

        private SSISDataSet.RequisitionDetailsRow getRowById(int formId, String itemId)
        {
            return ds.RequisitionDetails.FindByReqFormIdItemId(formId, itemId);
        }

        public int updateRequisitionDetails(RequisitionDetails rqDetails, int formId, String itemId)
        {
            SSISDataSet.RequisitionDetailsRow row = getRowById(formId, itemId);
            if (row != null)
            {
                row.RequestedQty = rqDetails.RequestedQty;
                row.ReceivedQty = rqDetails.ReceivedQty;
            }
           
            return detailstda.Update(ds.RequisitionDetails);
        }

        public int deleteRequisitionDetails(int formId,String itemId)
        {
            SSISDataSet.RequisitionDetailsRow row = getRowById(formId,itemId);
            if (row != null)
            {
                ds.RequisitionDetails.Rows.Remove(row);
            }
            return detailstda.Update(ds.RequisitionDetails);
        }

        public RequisitionDetails selectRequisitionDetails(int formId,String itemId)
        {
            SSISDataSet.RequisitionDetailsRow r = getRowById(formId, itemId);
            if (r != null)
            {
                reqDetails = new RequisitionDetails();
                reqDetails.ReqFormId = r.ReqFormId;
                reqDetails.ItemId = r.ItemId;
                reqDetails.RequestedQty = r.RequestedQty;
                reqDetails.ReceivedQty = r.ReceivedQty;
                return reqDetails;
            }
            else
            {
                return null;
            }
        }

        public List<RequisitionDetails> selectReqDetailsByReqId(int id)
        {
            
            reqDetails_td = detailstda.GetDataByReqId(id);

            if (reqDetails_td != null)
            {

                reqDetails_list = new List<RequisitionDetails>();
                for (int i = 0; i < reqDetails_td.Rows.Count; i++)
                {
                    reqDetails = new RequisitionDetails();
                    reqDetails.ReqFormId = reqDetails_td[i].ReqFormId;
                    reqDetails.ItemId = reqDetails_td[i].ItemId;
                    reqDetails.RequestedQty = reqDetails_td[i].RequestedQty;
                    reqDetails.ReceivedQty = reqDetails_td[i].ReceivedQty;

                    reqDetails_list.Add(reqDetails);
                }

                return reqDetails_list;
            }
            else
            {
                return null;
            }
        }

        //AMMT
        public List<ConsolidateRetrivalItem> getRequisitionDatabyItemId(String itemId)
        {
            consolidatetda.Fill(ds.ConsolidateRetrival,itemId);
            
            List<ConsolidateRetrivalItem> retrival_list = new List<ConsolidateRetrivalItem>();
            for (int i = 0; i < ds.ConsolidateRetrival.Rows.Count; i++)
            {
                ConsolidateRetrivalItem retrival_item = new ConsolidateRetrivalItem();
                retrival_item.ReqQty = ds.ConsolidateRetrival[i].ReqQty;
                retrival_item.DeptName = ds.ConsolidateRetrival[i].DeptName;
                retrival_item.CategoryName = ds.ConsolidateRetrival[i].CategoryName;
                retrival_item.Desc = ds.ConsolidateRetrival[i].Description;

                retrival_list.Add(retrival_item);
            }
            return retrival_list;
        }

        public List<Retrival> getItemsForRetrival(int categoryId)
        {
            retrivaltda.Fill(ds.Retrival, categoryId);
          
            if (ds.Retrival.Rows.Count > 0)
            {
                List<Retrival> ret_list = new List<Retrival>();
                for (int i = 0; i < ds.Retrival.Rows.Count; i++)
                {
                    Retrival r = new Retrival();
                    r.ItemId = ds.Retrival[i].ItemId;
                    r.Desc = ds.Retrival[i].Description;
                    r.BinId = ds.Retrival[i].BinId;
                    r.RequestedQty = ds.Retrival[i].RequestedQty;
                    r.ReceivedQty = ds.Retrival[i].ReceivedQty;
                    r.OutstandingQty = (ds.Retrival[i].RequestedQty - ds.Retrival[i].ReceivedQty);
                    ret_list.Add(r);
                }

                return ret_list;
            }

            else
            {
                return null;
            }
        }

        
    }
}
